﻿Option Strict On
Option Explicit On

Imports System.Data.Odbc
    Imports Oracle.ManagedDataAccess.Client
Public Class clsSQLDbIf
    ''' 
    ''' SQLコネクション
    ''' 
    Private _con As OdbcConnection = Nothing
    ''' 
    ''' トランザクション・オブジェクト
    ''' 
    ''' 
    Private _trn As OdbcTransaction = Nothing

    '''
    ''' 接続フラグ
    ''' 
    Private _connflg As Boolean = False

    Private SqlCmd As OdbcCommand

    Private WithEvents ConnectTimer As New Timer

    'Disconnect Timer Interval (Min)
    '>>2018/02/20 安部 切断タイマを秒指定に変更
    'Private Const MINUTS_TO_DISCONNECT = 1
    Private Const SECONDS_TO_DISCONNECT = 60
    '<<2018/02/20 安部 切断タイマを秒指定に変更

    Private it As Integer = 0

    Private strConnection As String = String.Empty

    ''' 
    ''' 
    ''' DB接続
    ''' 
    ''' データソース名
    ''' データベース名
    ''' ユーザーID
    ''' パスワード
    ''' タイムアウト値
    ''' 
    Public Function Connect(Optional ByVal drv As String = "",
                            Optional ByVal IPadr As String = "",
                            Optional ByVal dsn As String = "",
                            Optional ByVal dbn As String = "atlas",
                            Optional ByVal uid As String = "atlas",
                            Optional ByVal pas As String = "atlas",
                            Optional ByVal tot As Integer = -1) As Integer

        Dim szMess As String

        _connflg = False
        Connect = 0
        g_szDBIpAddress = ""

        Try
            If _con Is Nothing Then
                _con = New OdbcConnection
            End If

            Dim cst As String = ""
            cst = cst & "Driver={" & drv & "}"
            cst = cst & ";Server=" & IPadr
            'cst = cst & ";DSN=" & dsn
            cst = cst & ";Database=" & dbn
            cst = cst & ";UID=" & uid
            cst = cst & ";PWD=" & pas
            'cst = cst & ";MultipleActiveResultsSets=True"
            If tot > -1 Then
                '_con.ConnectionTimeout = tot
                cst = cst & ";Connect Timeout=" & tot.ToString
            End If
            _con.ConnectionString = cst

            strConnection = "Server=" & IPadr + ";Database=" & dbn + ";UID=" & uid + ";PWD=" & pas

            _con.Open()

            g_szDBIpAddress = _con.DataSource

            _connflg = True
            Connect = 1

            ConnectTimer.Start()
            it = 0

        Catch ex As Exception
            'Throw New Exception("Connect Error", ex)
            Connect = -1
            szMess = "データベースに接続できません" & vbCrLf & "Connect Error:" & ex.Message
            MsgBox(szMess)
        End Try
    End Function

    ''' 
    ''' DB切断
    ''' 
    Public Sub Disconnect()

        If False = _connflg Then
            Exit Sub
        End If

        Try
            _con.Close()
        Catch ex As Exception
            'Throw New Exception("Disconnect Error", ex)
            MsgBox("Disconnect Error:" & ex.Message)
        End Try
    End Sub

    ''' 
    ''' SQLの実行
    ''' 
    ''' SQL文
    ''' タイムアウト値
    ''' 
    ''' 
    Public Function ExecuteSql(ByVal sql As String,
                               Optional ByVal tot As Integer = -1) As DataTable

        Dim dt As New DataTable

        If False = _connflg Then
            Return dt
            Exit Function
        End If

        Try
            CheckConnection()
            Dim sqlCommand As New OdbcCommand(sql, _con, _trn)

            If tot > -1 Then
                sqlCommand.CommandTimeout = tot
            End If

            Dim adapter As New OdbcDataAdapter(sqlCommand)
            adapter.Fill(dt)
            adapter.Dispose()
            sqlCommand.Dispose()
        Catch ex As Exception
            'Throw New Exception("ExecuteSql Error", ex)
            MsgBox("ExecuteSql Error:" & ex.Message)
        End Try

        Return dt
    End Function
    Private Sub CheckConnection()
        If _con.State = ConnectionState.Closed Then
            'Debug.Print(Date.Now.ToString("mm:ss:ffff"))
            _con.Open()
            'Debug.Print(Date.Now.ToString("mm:ss:ffff"))
            'Connection Timer Start
            ConnectTimer.Start()
            'Debug.Print("Connection=" & _con.State.ToString)
        End If
        it = 0
        'Debug.Print("Checked")
    End Sub
End Class
